package com.huike.pmps.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.huike.pmps.dao.PODao;
import com.huike.pmps.model.constants.GlobalConstants;
import com.huike.pmps.model.dto.common.PageInfo;
import com.huike.pmps.model.entity.*;
import com.huike.pmps.model.entity.Dictionary;
import com.huike.pmps.model.vo.PageResult;
import com.huike.pmps.model.vo.Result;
import com.huike.pmps.model.vo.supplier.SupplierPoVo;
import com.huike.pmps.service.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 作者：谌贵斌(黑桃K)
 * 日期: 2888-08-08
 * 老师QQ: 272488352
 * 官方QQ交流群: 377748272
 * 刀客程序员官网：http://www.daoke360.com
 * 刀客程序员淘宝旗舰店：https://daoke360.taobao.com/
 */
@Service
@Slf4j
public class POServiceImpl implements POService {
    @Autowired
    private PODao poDao;
    @Autowired
    private DictionaryService dictionaryService;
    @Autowired
    private RegionService regionService;
    @Autowired
    private ProvincesService provincesService;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private POCourseService poCourseService;
    @Autowired
    private SupplierSettlementDetailService supplierSettlementDetailService;

    /**
     * 添加新合同
     *
     * @param po
     * @return
     */
    @Override
    public Result insertOne(PO po) {
        //新合同
        po.setCreateTime(new Date());
        po.setUpdateTime(new Date());
        po.setState(GlobalConstants.PO_STATE_INIT);
        //add by zcw 2021-07-05 设置PO状态为-未启动
        po.setPoState(GlobalConstants.PO_STATE_UNSTART);
        poDao.insert(po);
        return Result.success();
    }

    /**
     * 删除po或新合同
     *
     * @param id
     * @return
     */
    @Override
    public Result deleteById(Integer id) {
        Result result = poCourseService.selectByPoId(id);
        if (result.getData() != null) return Result.failure("请先删除PO下的课程");
        poDao.deleteById(id);
        return Result.success();
    }


    /**
     * 获取po列表
     */
    @Override
    public Result selectByCondition(Member member, PageInfo pageInfo, String poCode, String customerName, String contractNo, String regionName, String provinceName,String teamName, Integer poState) {
        QueryWrapper<PO> queryWrapper = new QueryWrapper<>();

        if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_ADMIN)) {//管理员
        } else if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_REGION)) {//战区司令
            if (member.getRegionId() == null) return Result.failure("您的账号没有配置所属战区，请联系 系统管理员 进行战区配置");
            queryWrapper.eq("region_id", member.getRegionId());
        } else {
            if (member.getProvinceId() == null) return Result.failure("您的账号没有配置所属省区，请联系 系统管理员 进行省区配置");
            queryWrapper.eq("province_id", member.getProvinceId());
        }

        queryWrapper.eq("state", GlobalConstants.PO_STATE_CODE);
        if (StringUtils.isNotBlank(poCode))
            queryWrapper.like("po", poCode);
        if (StringUtils.isNotBlank(customerName))
            queryWrapper.like("customer_name", customerName);
        if (StringUtils.isNotBlank(contractNo))
            queryWrapper.like("contract_no", contractNo);
        if (StringUtils.isNotBlank(regionName))
            queryWrapper.like("region_name", regionName);
        if (StringUtils.isNotBlank(provinceName))
            queryWrapper.like("province_name", provinceName);
        if (StringUtils.isNotBlank(teamName))
            queryWrapper.like("team_name", teamName);
        if (poState != null)
            queryWrapper.eq("po_state", poState);

        queryWrapper.orderByDesc("id");

        Page<PO> page = new Page<>(pageInfo.getPage(), pageInfo.getPageSize());
        Page<PO> pageList = poDao.selectPage(page, queryWrapper);

        if (pageList.getSize() > 0) {

            // update by zcw 2021-01-29 查询PO列表返回 预估金额 未支付 已支付 ---------------------start
            Map<String, SupplierPoVo> map = supplierSettlementDetailService.selectSumCostAndPaidByPo();
            //获取所有的PO单号
            Set<String> set = map.keySet();
            //获取分页查询出来的PO
            List<PO> pos = pageList.getRecords();
            for(PO po : pos){
                if(set.contains(po.getPo())){
                    SupplierPoVo supplierPoVo = map.get(po.getPo());
                    po.setUnpaidAmountSum(supplierPoVo.getUnpaidAmountSum());
                    po.setPaidAmountSum(supplierPoVo.getPaidAmountSum());
                    po.setEstimatedGenerationCostSum(supplierPoVo.getEstimatedGenerationCostSum());
                }
            }
            // update by zcw 2021-01-29 查询PO列表返回 预估金额 未支付 已支付 ---------------------end
            PageResult pageResult = PageResult.builder().pageNumber(pageList.getCurrent()).pageSize(pageList.getSize())
                    .total(pageList.getTotal()).rows(pos).build();
            return Result.success(pageResult);
        } else {
            return Result.success("没有符合条件的记录");
        }

    }

    /**
     * 获取新合同列表
     *
     * @param pageInfo
     * @param customerName
     * @return
     */
    @Override
    public Result selectByCondition(Member member, PageInfo pageInfo, String customerName) {
        QueryWrapper<PO> queryWrapper = new QueryWrapper<>();

        if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_ADMIN)) {//管理员
        } else if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_REGION)) {//战区司令
            if (member.getRegionId() == null) return Result.failure("您的账号没有配置所属战区，请联系 系统管理员 进行战区配置");
            queryWrapper.eq("region_id", member.getRegionId());
        } else if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_PROVINCE) || member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_COURSE_CHARGE)) {//省或课程编导
            if (member.getProvinceId() == null) return Result.failure("您的账号没有配置所属省区，请联系 系统管理员 进行省区配置");
            queryWrapper.eq("province_id", member.getProvinceId());
        } else {
            return Result.failure("您无权查看任何数据");
        }


        queryWrapper.eq("state", GlobalConstants.PO_STATE_INIT);
        if (StringUtils.isNotBlank(customerName)) {
            queryWrapper.like("customer_name", customerName);
        }
        queryWrapper.orderByDesc("id");

        Page<PO> page = new Page<>(pageInfo.getPage(), pageInfo.getPageSize());
        Page<PO> pageList = poDao.selectPage(page, queryWrapper);
        if (pageList.getSize() > 0) {
            PageResult pageResult = PageResult.builder().pageNumber(pageList.getCurrent()).pageSize(pageList.getSize())
                    .total(pageList.getTotal()).rows(pageList.getRecords()).build();
            return Result.success(pageResult);
        } else {
            return Result.success("没有符合条件的记录");
        }
    }


    /**
     * 更新新合同或PO数据
     *
     * @param po
     * @return
     */
    @Override
    public Result updateById(PO po) {
        po.setRowEdit(false);
        //add by zcw 2021-07-05 设置PO状态为进行中
        //po.setPoState(1);
        //更新时间
        //po.setUpdateTime(new Date());
        if (poDao.updateById(po) > 0) {
            poDao.updatePOContractFinishedTimeLengthByPoId(po.getId());
            poDao.updatePODependence(po);
            return Result.success();
        } else {
            return Result.failure("没有可更新的内容");
        }
    }

    /**
     * 通过id获取新合同或PO数据
     *
     * @param id
     * @return
     */
    @Override
    public Result selectById(Integer id) {
        PO po = poDao.selectById(id);
        if (po != null) {
            return Result.success(po);
        } else {
            return Result.failure("找不到数据");
        }
    }


    /**
     * 生成po码
     * yyyyMM-产品类型 产品线
     *
     * @param ids
     * @return
     */
    @Override
    public Result updateAndGeneratorPO(String ids) {
        String[] items = ids.split(",");
        for (String item : items) {
            Integer id = Integer.valueOf(item);

            PO po = poDao.selectById(id);
            if (po.getProductTypeId() == null) {
                return Result.failure("合同编号：" + po.getContractNo() + "生成PO码失败：新合同产品类型为空，请检查是否有值");
            }
            //获取产品类型 字母简称
            String productType = dictionaryService.selectByOne(po.getProductTypeId().toString()).getValue();

            String yearMoth = new SimpleDateFormat("yyyyMM").format(new Date());

            //从redis中获取当月合同序号
            Long contractNo = redisTemplate.opsForHash().increment(GlobalConstants.REDIS_HASH_CONTRACT_NO, yearMoth, 1);

            String contractNoStr = "";
            if (contractNo > 0 && contractNo < 10) {
                contractNoStr = "00" + contractNo;
            } else if (contractNo >= 10 && contractNo < 100) {
                contractNoStr = "0" + contractNo;
            } else {
                contractNoStr = "" + contractNo;
            }
            //生成po码(样例：202010-GZ001)
            String poCode = yearMoth + "-" + productType + contractNoStr;
            PO newPO = PO.builder().id(id).po(poCode).poState(GlobalConstants.PO_STATE_START).state(GlobalConstants.PO_STATE_CODE).updateTime(new Date()).build();
            poDao.updateById(newPO);
        }
        return Result.success("生成PO单号成功，请到PO列表管理查看");
    }

    /**
     * 导入新合同
     *
     * @param file
     * @return
     */
    @Override
    public Result insertByImportNewContract(MultipartFile file) {
        if (file.isEmpty()) {
            return Result.failure("文件为空");
        }
        try {
            //获取当前系统中合同编号
            List<String> allContractNo = this.selectAllContractNo();
            //根据路径获取这个操作excel的实例
            XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
            //根据页面index 获取sheet页
            XSSFSheet sheet = wb.getSheetAt(0);
            //获取标题行
            XSSFRow titleRow = sheet.getRow(0);
            String[] titleRowArray = new String[]{"合同编号", "客户名称", "合同金额", "合同申请人", "合同交付日期", "合同总时长", "省份", "产品类型"};
            for (int i = 0; i < titleRowArray.length; i++) {
                if (!titleRowArray[i].equals(titleRow.getCell(i).toString()))
                    return Result.failure("合同模板标题行已被修改，请勿修改合同模板标题行");
            }
            //日期格式化
            //SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            //合同重复记录
            StringBuffer repeatContractBuffer = new StringBuffer();
            //合同重复记录数据
            Integer repeatContractRecord = 0;

            //实体类集合
            List<PO> poList = new ArrayList<>();
            XSSFRow row = null;

            //循环sesheet页中数据从第二行开始，第一行是标题
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行数据
                row = sheet.getRow(i);

                //合同编号
                if (row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).toString()))
                    return Result.failure("本次导入全部失败，第【" + i + "】条数据的合同编号为空，请补全！");

                String contractNo = row.getCell(0).toString();
                if (allContractNo.contains(contractNo)) {
                    repeatContractBuffer.append(contractNo).append("    ");
                    repeatContractRecord++;
                    continue;
                }
                //客户名称
                String customerName = row.getCell(1) != null && StringUtils.isNotBlank(row.getCell(1).toString()) ? row.getCell(1).toString() : null;
                //合同金额
                XSSFCell contractAmountCell = row.getCell(2);
                BigDecimal contractAmount = contractAmountCell != null && StringUtils.isNotBlank(contractAmountCell.toString()) ? BigDecimal.valueOf(Double.valueOf(contractAmountCell.toString())) : null;
                //合同申请人
                String contractApplicant = row.getCell(3) != null && StringUtils.isNotBlank(row.getCell(3).toString()) ? row.getCell(3).toString() : null;
                //合同交付日期
                if (row.getCell(4) == null || StringUtils.isBlank(row.getCell(4).toString()))
                    return Result.failure("本次导入全部失败，第【" + i + "】条数据的 合同交付日期为空，请补全！");
                Date contractDeliveryDate = row.getCell(4).getDateCellValue();

                //合同总时长
                XSSFCell contractTotalTimeLengthCell = row.getCell(5);
                Integer contractTotalTimeLength = contractTotalTimeLengthCell != null && StringUtils.isNotBlank(contractTotalTimeLengthCell.toString()) ? Double.valueOf(contractTotalTimeLengthCell.toString()).intValue() : null;

                //省份 ;
                Integer provinceId = null;
                String provinceName = null;
                Integer regionId = null;
                String regionName = null;
                if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).toString())) {
                    Provinces provinces = provincesService.selectByProvinceName(row.getCell(6).toString());
                    provinceId = provinces.getProvinceId();
                    provinceName = provinces.getProvince();
                    Region region = regionService.selectParentRegionByProvinceName(provinces.getProvince());
                    if (region != null) {
                        regionId = region.getId();
                        regionName = region.getRegion();
                    }
                }


                //产品类型
                Integer productTypeId = null;
                String productTypeName = null;
                if (row.getCell(7) != null && StringUtils.isNotBlank(row.getCell(7).toString())) {
                    Dictionary dictionary = dictionaryService.selectByOne(row.getCell(7).toString(), GlobalConstants.DICTIONARY_PRODUCT_TYPE);
                    productTypeId = dictionary.getId();
                    productTypeName = dictionary.getName();
                }
                //构建PO实体
                PO po = PO.builder().contractNo(contractNo).customerName(customerName).contractAmount(contractAmount)
                        .contractApplicant(contractApplicant).contractDeliveryDate(contractDeliveryDate).contractTotalTimeLength(contractTotalTimeLength)
                        .provinceId(provinceId).provinceName(provinceName).productTypeId(productTypeId).productTypeName(productTypeName)
                        .regionId(regionId).regionName(regionName).createTime(new Date())
                        //导入标记(1导入新合同,2导入po数据
                        .importFlag(1)
                        //PO记录状态(1新合同，2生成了po码)
                        .state(GlobalConstants.PO_STATE_INIT)
                        .updateTime(new Date()).createTime(new Date()).build();
                poList.add(po);
            }
            if (poList.size() > 0)
                poDao.insertPOBatch(poList);
            return Result.success("总共记录数：" + (sheet.getPhysicalNumberOfRows() - 1) + "条,成功导入：" + (sheet.getPhysicalNumberOfRows() - 1 - repeatContractRecord) + "条,合同编号重复：" + repeatContractRecord + "条，重复合同编号分别是：" + repeatContractBuffer.toString());
        } catch (Exception e) {
            log.error(e.getMessage());
            return Result.failure("新合同导入失败，请检查数据格式是否符合模板要求");
        }
    }

    /**
     * 导入PO
     *
     * @param file
     * @return
     */
    @Override
    public Result insertByImportOldPo(MultipartFile file) {
        if (file.isEmpty()) {
            return Result.failure("文件为空");
        }
        try {
            //获取当前系统中合同编号和PO单号
            List<String> allContractNo = this.selectAllContractNo();
            List<String> allPO = this.selectAllPO();
            //根据路径获取这个操作excel的实例
            XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
            //根据页面index 获取sheet页
            XSSFSheet sheet = wb.getSheetAt(0);
            //获取标题行
            XSSFRow titleRow = sheet.getRow(0);
            String[] titleRowArray = new String[]{"PO单号", "合同编号", "客户名称", "合同金额", "合同申请人", "合同交付日期", "战区", "省份", "产品类型", "合同总时长", "已完成时长", "未完成时长", "已回款", "未回款", "版权归属", "合同毛利", "预测毛利率", "预估生成成本", "PO状态", "约定售后期限","PO备注"};
            for (int i = 0; i < titleRowArray.length; i++) {
                if (!titleRowArray[i].equals(titleRow.getCell(i).toString()))
                    return Result.failure("模板标题行已被修改，请勿修改合同模板标题行");
            }
            //日期格式化
            //SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
            //实体类集合
            List<PO> poList = new ArrayList<>();
            XSSFRow row = null;

            //合同重复记录
            StringBuffer repeatContractBuffer = new StringBuffer();
            //合同重复记录数据
            Integer repeatContractRecord = 0;

            //循环sesheet页中数据从第二行开始，第一行是标题
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行数据
                row = sheet.getRow(i);

                if (row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).toString()))
                    return Result.success("第" + i + "条数据po单号为空,本次全部导入失败");

                String poNo = row.getCell(0).toString();
                if (allPO.contains(poNo)) {
                    return Result.success("第" + i + "条数据,系统中已存在该PO单号：" + poNo + ",本次全部导入失败");
                }
                //合同编号
                String contractNo = null;
                if (row.getCell(1) != null && StringUtils.isNotBlank(row.getCell(1).toString())) {
                    contractNo = row.getCell(1).toString();
                    if (allContractNo.contains(contractNo)) {
                        return Result.success("第" + i + "条数据,系统中已存在该合同编号：" + contractNo + ",本次全部导入失败");
                    }
                } else {
                    return Result.success("第" + i + "条数据合同编号为空,本次全部导入失败");
                }
                //客户名称
                String customerName = row.getCell(2) != null && StringUtils.isNotBlank(row.getCell(2).toString()) ? row.getCell(2).toString() : null;
                //合同金额
                XSSFCell contractAmountCell = row.getCell(3);
                BigDecimal contractAmount = contractAmountCell != null && StringUtils.isNotBlank(contractAmountCell.toString()) ? BigDecimal.valueOf(Double.valueOf(contractAmountCell.toString())) : null;
                //合同申请人
                String contractApplicant = row.getCell(4) != null && StringUtils.isNotBlank(row.getCell(4).toString()) ? row.getCell(4).toString() : null;
                //合同交付日期
                Date contractDeliveryDate = row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).toString()) ? row.getCell(5).getDateCellValue() : null;

                //战区
                XSSFCell regionNameCell = row.getCell(6);
                Integer regionId = null;
                String regionName = null;
                if (regionNameCell != null && StringUtils.isNotBlank(regionNameCell.toString())) {
                    Region region = regionService.selectOneByRegionName(regionNameCell.toString().substring(0, 2));
                    if (region != null) {
                        regionId = region.getId();
                        regionName = region.getRegion();
                    } else {
                        return Result.failure("本次导入全部失败，第【" + i + "】条数据的战区无法在系统中查找到,请确保表格中战区名字与系统中存在的战区名字保持一致");
                    }
                }


                //省份
                Integer provinceId = null;
                String provinceName = null;
                if (row.getCell(7) != null && StringUtils.isNotBlank(row.getCell(7).toString())) {
                    Provinces provinces = provincesService.selectByProvinceName(row.getCell(7).toString());
                    if (provinces == null)
                        return Result.failure("本次导入全部失败，第【" + i + "】条数据的 省份: " + provinceName + ", 无法在系统中查找到,请确保表格中战区名字与系统中存在的战区名字保持一致");
                    provinceId = provinces.getProvinceId();
                    provinceName = provinces.getProvince();
                }
                //产品类型
                Integer productTypeId = null;
                String productTypeName = null;
                if (row.getCell(8) != null && StringUtils.isNotBlank(row.getCell(8).toString())) {
                    Dictionary dictionary = dictionaryService.selectByOne(row.getCell(8).toString(), GlobalConstants.DICTIONARY_PRODUCT_TYPE);
                    productTypeId = dictionary.getId();
                    productTypeName = dictionary.getName();
                }
                //合同总时长
                XSSFCell contractTotalTimeLengthCell = row.getCell(9);
                Integer contractTotalTimeLength = contractTotalTimeLengthCell != null && StringUtils.isNotBlank(contractTotalTimeLengthCell.toString()) ? Double.valueOf(contractTotalTimeLengthCell.toString()).intValue() : null;
                //合同已完成时长
                XSSFCell contractFinishedTimeLengthCell = row.getCell(10);
                Integer contractFinishedTimeLength = contractFinishedTimeLengthCell != null && StringUtils.isNotBlank(contractFinishedTimeLengthCell.toString()) ? Double.valueOf(contractFinishedTimeLengthCell.toString()).intValue() : null;
                //合同未完成时长
                XSSFCell contractUnfinishedTimeLengthCell = row.getCell(11);
                Integer contractUnfinishedTimeLength = contractUnfinishedTimeLengthCell != null && StringUtils.isNotBlank(contractUnfinishedTimeLengthCell.toString()) ? Double.valueOf(contractUnfinishedTimeLengthCell.toString()).intValue() : null;
                //合同已回款金额
                XSSFCell contractPaymentReceivedCell = row.getCell(12);
                BigDecimal contractPaymentReceived = contractPaymentReceivedCell != null && StringUtils.isNotBlank(contractPaymentReceivedCell.toString()) ? BigDecimal.valueOf(Double.valueOf(contractPaymentReceivedCell.toString())) : null;
                //合同未回款金额
                XSSFCell contractOutstandingPaymentCell = row.getCell(13);
                BigDecimal contractOutstandingPayment = contractOutstandingPaymentCell != null && StringUtils.isNotBlank(contractOutstandingPaymentCell.toString()) ? BigDecimal.valueOf(Double.valueOf(contractOutstandingPaymentCell.toString())) : null;
                //版权归属
                String copyrightOwnership = row.getCell(14) != null && StringUtils.isNotBlank(row.getCell(14).toString()) ? row.getCell(14).toString() : null;
                //合同毛利
                XSSFCell contractGrossProfitCell = row.getCell(15);
                BigDecimal contractGrossProfit = contractGrossProfitCell != null && StringUtils.isNotBlank(contractGrossProfitCell.toString()) ? BigDecimal.valueOf(Double.valueOf(contractGrossProfitCell.toString())) : null;
                //预测毛利率
                String forecastGrossProfitRate = row.getCell(16) != null && StringUtils.isNotBlank(row.getCell(16).toString()) ? row.getCell(16).toString() : null;
                //预估生成成本
                XSSFCell estimatedGenerationCostCell = row.getCell(17);
                BigDecimal estimatedGenerationCost = estimatedGenerationCostCell != null && StringUtils.isNotBlank(estimatedGenerationCostCell.toString()) ? BigDecimal.valueOf(Double.valueOf(estimatedGenerationCostCell.toString())) : null;
                //po状态(1未启动、2进行中、3已交付、4已验收、5已完结、6已终止)
                Integer poState = null;
                if (row.getCell(18) != null && StringUtils.isNotBlank(row.getCell(18).toString())) {
                    String poStateStr = row.getCell(18).toString();
                    if (StringUtils.isNotBlank(poStateStr)) {
                        poState = poStateStr.contains("完成") ? GlobalConstants.PO_STATE_FINISH : poStateStr.contains("进行") ? GlobalConstants.PO_STATE_START : poStateStr.contains("交付") ? GlobalConstants.PO_STATE_DELIVER : poStateStr.contains("验收")? GlobalConstants.PO_STATE_RECEPT:poStateStr.contains("终止")? GlobalConstants.PO_STATE_OVER:GlobalConstants.PO_STATE_UNSTART;
                    }
                }
                //约定售后期限
                Date afterSalePeriod = row.getCell(19) != null && StringUtils.isNotBlank(row.getCell(19).toString()) ? row.getCell(19).getDateCellValue() : null;

                //po备注
                String poRemark = row.getCell(20) != null && StringUtils.isNotBlank(row.getCell(20).toString()) ? row.getCell(20).toString() : null;

                //构建PO实体
                PO po = PO.builder().po(poNo).contractNo(contractNo).customerName(customerName).contractAmount(contractAmount)
                        .contractApplicant(contractApplicant).contractDeliveryDate(contractDeliveryDate).contractTotalTimeLength(contractTotalTimeLength)
                        .regionId(regionId).regionName(regionName).provinceId(provinceId).provinceName(provinceName).productTypeId(productTypeId).productTypeName(productTypeName)
                        .contractFinishedTimeLength(contractFinishedTimeLength).contractUnfinishedTimeLength(contractUnfinishedTimeLength)
                        .contractPaymentReceived(contractPaymentReceived).contractOutstandingPayment(contractOutstandingPayment)
                        .copyrightOwnership(copyrightOwnership).contractGrossProfit(contractGrossProfit).forecastGrossProfitRate(forecastGrossProfitRate)
                        .estimatedGenerationCost(estimatedGenerationCost).poState(poState).afterSalePeriod(afterSalePeriod).poRemark(poRemark)
                        .updateTime(new Date()).createTime(new Date())
                        .importFlag(2)
                        .state(GlobalConstants.PO_STATE_CODE)
                        .build();
                poList.add(po);
            }
            if (poList.size() > 0) {
                poDao.insertPOBatch(poList);
                return Result.success("总共记录数：" + (sheet.getPhysicalNumberOfRows() - 1) + "条,成功导入：" + (sheet.getPhysicalNumberOfRows() - repeatContractRecord - 1) + "条,合同编号重复：" + repeatContractRecord + "，重复合同编号分别是：" + repeatContractBuffer.toString());
            } else {
                return Result.failure("表格中没有任何数据");
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
            return Result.failure("PO导入失败，请检查数据格式是否符合模板要求");
        }
    }

    /**
     * po导出
     *
     * @param response
     * @param customerName
     */
    @Override
    public void exportPO(HttpServletResponse response, String poCode, String customerName, String contractNo, String regionName, String provinceName,String teamName, Integer poState, Member member) {
        QueryWrapper<PO> queryWrapper = new QueryWrapper<>();


        if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_ADMIN)) {//管理员
        } else if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_REGION)) {//战区司令
            queryWrapper.eq("region_id", member.getRegionId());
        } else {
            queryWrapper.eq("province_id", member.getProvinceId());
        }

        //导出po，记录状态为2
        queryWrapper.eq("state", GlobalConstants.PO_STATE_CODE);
        if (StringUtils.isNotBlank(poCode))
            queryWrapper.like("po", poCode);
        if (StringUtils.isNotBlank(customerName))
            queryWrapper.like("customer_name", customerName);
        if (StringUtils.isNotBlank(contractNo))
            queryWrapper.like("contract_no", contractNo);
        if (StringUtils.isNotBlank(regionName))
            queryWrapper.like("region_name", regionName);
        if (StringUtils.isNotBlank(provinceName))
            queryWrapper.like("province_name", provinceName);
        if (StringUtils.isNotBlank(teamName))
            queryWrapper.like("team_name", teamName);
        if (poState != null)
            queryWrapper.eq("po_state", poState);
        List<PO> poList = poDao.selectList(queryWrapper);
        // update by zcw 2021-01-29 查询PO列表返回 预估金额 未支付 已支付 ---------------------start
        Map<String, SupplierPoVo> map = supplierSettlementDetailService.selectSumCostAndPaidByPo();
        //获取所有的PO单号
        Set<String> set = map.keySet();
        //获取分页查询出来的PO
        for(PO po : poList){
            if(set.contains(po.getPo())){
                SupplierPoVo supplierPoVo = map.get(po.getPo());
                po.setUnpaidAmountSum(supplierPoVo.getUnpaidAmountSum());
                po.setPaidAmountSum(supplierPoVo.getPaidAmountSum());
                po.setEstimatedGenerationCostSum(supplierPoVo.getEstimatedGenerationCostSum());
            }
        }
        // update by zcw 2021-01-29 查询PO列表返回 预估金额 未支付 已支付 ---------------------end
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("export_excel/po_template.xlsx");
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(in);
            //获取第一个工作表sheet
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
            for (int i = 0; i < poList.size(); i++) {
                PO po = poList.get(i);
                XSSFRow row = sheetAt.createRow(i + 1);

                //供应商名称
                XSSFCell poCell = row.createCell(0);
                poCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getPo()))
                    poCell.setCellValue(po.getPo());


                //合同编号
                XSSFCell contractNoCell = row.createCell(1);
                contractNoCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getContractNo()))
                    contractNoCell.setCellValue(po.getContractNo());


                //客户名称
                XSSFCell customerNameCell = row.createCell(2);
                customerNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getCustomerName()))
                    customerNameCell.setCellValue(po.getCustomerName());

                //合同金额
                XSSFCell contractAmountCell = row.createCell(3);
                contractAmountCell.setCellType(CellType.STRING);
                contractAmountCell.setCellValue(po.getContractAmount() == null ? "" : po.getContractAmount().toString());
                //合同申请人
                XSSFCell contractApplicantCell = row.createCell(4);
                contractApplicantCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getContractApplicant()))
                    contractApplicantCell.setCellValue(po.getContractApplicant());

                //合同交付日期
                XSSFCell contractDeliveryDateCell = row.createCell(5);
                contractDeliveryDateCell.setCellType(CellType.STRING);
                contractDeliveryDateCell.setCellValue(po.getContractDeliveryDate() == null ? "" : simpleDateFormat.format(po.getContractDeliveryDate()));


                //战区
                XSSFCell regionNameCell = row.createCell(6);
                regionNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getRegionName()))
                    regionNameCell.setCellValue(po.getRegionName());

                //省份名字
                XSSFCell provinceNameCell = row.createCell(7);
                provinceNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getProvinceName()))
                    provinceNameCell.setCellValue(po.getProvinceName());

                //客户成功名字
                XSSFCell teamNameCell = row.createCell(8);
                teamNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getTeamName()))
                    teamNameCell.setCellValue(po.getTeamName());

                //产品类型名称
                XSSFCell productTypeNameCell = row.createCell(9);
                productTypeNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getProductTypeName()))
                    productTypeNameCell.setCellValue(po.getProductTypeName());


                //合同总时长
                XSSFCell contractTotalTimeLengthCell = row.createCell(10);
                contractTotalTimeLengthCell.setCellType(CellType.STRING);
                contractTotalTimeLengthCell.setCellValue(po.getContractTotalTimeLength() == null ? "" : po.getContractTotalTimeLength().toString());
                //合同已完成时长
                XSSFCell contractFinishedTimeLengthCell = row.createCell(11);
                contractFinishedTimeLengthCell.setCellType(CellType.STRING);
                contractFinishedTimeLengthCell.setCellValue(po.getContractFinishedTimeLength() == null ? "" : po.getContractFinishedTimeLength().toString());

                //合同未完成时长
                XSSFCell contractUnfinishedTimeLengthCell = row.createCell(12);
                contractUnfinishedTimeLengthCell.setCellType(CellType.STRING);
                contractUnfinishedTimeLengthCell.setCellValue(po.getContractUnfinishedTimeLength() == null ? "" : po.getContractUnfinishedTimeLength().toString());
                //合同已回款金额
                XSSFCell contractPaymentReceivedCell = row.createCell(13);
                contractPaymentReceivedCell.setCellType(CellType.STRING);
                contractPaymentReceivedCell.setCellValue(po.getContractPaymentReceived() == null ? "" : po.getContractPaymentReceived().toString());
                //合同未回款金额
                XSSFCell contractOutstandingPaymentCell = row.createCell(14);
                contractOutstandingPaymentCell.setCellType(CellType.STRING);
                contractOutstandingPaymentCell.setCellValue(po.getContractOutstandingPayment() == null ? "" : po.getContractOutstandingPayment().toString());

                //版权归属
                XSSFCell copyrightOwnershipCell = row.createCell(15);
                copyrightOwnershipCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getCopyrightOwnership()))
                    copyrightOwnershipCell.setCellValue(po.getCopyrightOwnership());

                //合同毛利
                XSSFCell contractGrossProfitCell = row.createCell(16);
                contractGrossProfitCell.setCellType(CellType.STRING);
                contractGrossProfitCell.setCellValue(po.getContractGrossProfit() == null ? "" : po.getContractGrossProfit().toString());

                //合同毛利率
                XSSFCell forecastGrossProfitRateCell = row.createCell(17);
                forecastGrossProfitRateCell.setCellType(CellType.STRING);
                if (po.getForecastGrossProfitRate() != null)
                    forecastGrossProfitRateCell.setCellValue(po.getForecastGrossProfitRate());

                //预估生成成本
                XSSFCell estimatedGenerationCostCell = row.createCell(18);
                estimatedGenerationCostCell.setCellType(CellType.STRING);
                estimatedGenerationCostCell.setCellValue(po.getEstimatedGenerationCost() == null ? "" : po.getEstimatedGenerationCost().toString());

                // po状态(1未启动、2进行中、3已交付、4已验收、5已完结、6已终止、7已过期)
                XSSFCell poStateCell = row.createCell(19);
                poStateCell.setCellType(CellType.STRING);
                if(po.getPoState() !=null){
                    switch(po.getPoState()){
                        case 1:
                            poStateCell.setCellValue("未启动");
                            break;
                        case 2:
                            poStateCell.setCellValue("进行中");
                            break;
                        case 3:
                            poStateCell.setCellValue("已交付");
                            break;
                        case 4:
                            poStateCell.setCellValue("已验收");
                            break;
                        case 5:
                            poStateCell.setCellValue("已完结");
                            break;
                        case 6:
                            poStateCell.setCellValue("已终止");
                            break;
                        case 7:
                            poStateCell.setCellValue("已过期");
                            break;
                        default:
                            poStateCell.setCellValue("未知状态");
                    }
                    //poStateCell.setCellValue(po.getPoState() == 1 ? "进行中" : po.getPoState() == 2 ? "已完成" : "已过期");
                }

                //约定售后期限
                XSSFCell afterSalePeriodCell = row.createCell(20);
                afterSalePeriodCell.setCellType(CellType.STRING);
                afterSalePeriodCell.setCellValue(po.getAfterSalePeriod() == null ? "" : simpleDateFormat.format(po.getAfterSalePeriod()));

                //创建时间
                XSSFCell createTimeCell = row.createCell(21);
                createTimeCell.setCellType(CellType.STRING);
                if (po.getCreateTime() != null)
                    createTimeCell.setCellValue(simpleDateFormat.format(po.getCreateTime()));

                //PO备注
                XSSFCell poRemarkCell = row.createCell(22);
                poRemarkCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getPoRemark()))
                    poRemarkCell.setCellValue(po.getPoRemark());

                //风险计提
                XSSFCell riskProvisionCell = row.createCell(23);
                riskProvisionCell.setCellType(CellType.STRING);
                riskProvisionCell.setCellValue(po.getRiskProvision() == null ? "" : po.getRiskProvision().toString());

                //风险利率
                XSSFCell riskRateCell = row.createCell(24);
                riskRateCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(po.getRiskRate()))
                    riskRateCell.setCellValue(po.getRiskRate());

                //渠道费用
                XSSFCell channelFeeCell = row.createCell(25);
                channelFeeCell.setCellType(CellType.STRING);
                channelFeeCell.setCellValue(po.getChannelFee() == null ? "" : po.getChannelFee().toString());

                //产学合作费
                XSSFCell industryAndUniversityCooperationFeeCell = row.createCell(26);
                industryAndUniversityCooperationFeeCell.setCellType(CellType.STRING);
                industryAndUniversityCooperationFeeCell.setCellValue(po.getIndustryAndUniversityCooperationFee() == null ? "" : po.getIndustryAndUniversityCooperationFee().toString());

                //其他
                XSSFCell otherCell = row.createCell(27);
                otherCell.setCellType(CellType.STRING);
                otherCell.setCellValue(po.getOther() == null ? "" : po.getOther().toString());

                //预估金额
                XSSFCell estimatedGenerationCostSumCell = row.createCell(28);
                estimatedGenerationCostSumCell.setCellType(CellType.STRING);
                estimatedGenerationCostSumCell.setCellValue(po.getEstimatedGenerationCostSum() == null ? "" : po.getEstimatedGenerationCostSum().toString());

                //未支付
                XSSFCell unpaidAmountSumCell = row.createCell(29);
                unpaidAmountSumCell.setCellType(CellType.STRING);
                unpaidAmountSumCell.setCellValue(po.getUnpaidAmountSum() == null ? "" : po.getUnpaidAmountSum().toString());

                //已支付
                XSSFCell paidAmountSumCell = row.createCell(30);
                paidAmountSumCell.setCellType(CellType.STRING);
                paidAmountSumCell.setCellValue(po.getPaidAmountSum() == null ? "" : po.getPaidAmountSum().toString());

            }
            String fileName = "po_" + new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()) + ".xlsx";
            response.setContentType("application/octet-stream"); // 响应内容格式
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            ServletOutputStream outputStream = response.getOutputStream();
            xssfWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            xssfWorkbook.close();
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public PO selectOne(String po) {
        QueryWrapper<PO> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("po", po);
        return poDao.selectOne(queryWrapper);
    }

    private List<String> selectAllContractNo() {
        QueryWrapper<PO> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("distinct contract_no");
        queryWrapper.isNotNull("contract_no");
        List<Object> objects = poDao.selectObjs(queryWrapper);
        List<String> contractNoList = new ArrayList<>();
        for (Object object : objects) {
            contractNoList.add(object.toString());
        }
        return contractNoList;
    }

    private List<String> selectAllPO() {
        QueryWrapper<PO> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("distinct po");
        queryWrapper.isNotNull("po");
        List<Object> objects = poDao.selectObjs(queryWrapper);
        List<String> poList = new ArrayList<>();
        for (Object object : objects) {
            poList.add(object.toString());
        }
        return poList;
    }
}